﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Windows.Forms;
using Db4objects.Db4o;
using NmbpProject2.DboClasses;
using Oracle.DataAccess.Client;

namespace NmbpProject2.DataLayer
{
    internal class ProgLanguageDL
    {
        public ProgLanguage findByName(String name)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    IList<ProgLanguage> languages =
                DataConfig.db.Query(delegate(ProgLanguage match) { return match.Name == name; });
                    if (languages.Count > 0)
                    {
                        ProgLanguage language = languages[0];
                        return language;
                    }
                    return null;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var dt = new DataTable();
                        var cmd = new OracleCommand("select name from languages where name='" + name + "'", oracle.conn);
                        cmd.CommandType = CommandType.Text;
                        new OracleDataAdapter(cmd).Fill(dt);
                        var lang = new ProgLanguage(dt.Rows[0][0].ToString());
                        return lang;
                    }
                    catch (Exception e)
                    {
                    }
                    break;

            }
            return null;


        }

        public void insertLanguage(string name)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    ProgLanguage language = new ProgLanguage(name);
                    DataConfig.db.Store(language);
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand();
                        cmd.Connection = oracle.conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = string.Format("insert into languages values ('{0}')", name);
                        cmd.ExecuteNonQuery();
                        oracle.closeConnection();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;

            }
        }

        /// <summary>
        /// zak
        /// radi i za oracle
        /// </summary>
        /// <returns></returns>
        public List<ProgLanguage> getAllLangs()
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    IObjectSet result = DataConfig.db.QueryByExample(typeof(ProgLanguage));
                    List<ProgLanguage> langs = new List<ProgLanguage>();
                    foreach (ProgLanguage language in result)
                    {
                        langs.Add(language);
                    }
                    return langs;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var dt = new DataTable();
                        var cmd = new OracleCommand("select name from languages", oracle.conn);
                        cmd.CommandType = CommandType.Text;
                        new OracleDataAdapter(cmd).Fill(dt);
                        var lang = new List<ProgLanguage>();
                        foreach (DataRow row in dt.Rows)
                        {
                            lang.Add(new ProgLanguage(row[0].ToString()));
                        }
                        oracle.closeConnection();
                        return lang;
                        
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
            }
            return null;
        }

        /// <summary>
        /// vraca iz oracla sve jezike po uvijetu.
        /// dodao:zak
        /// </summary>
        /// <param name="table">polazna tablica</param>
        /// <param name="condition">uvijet dohvata</param>
        /// <returns>lista prog jezika</returns>
        public static List<ProgLanguage> getAllLanguages(string table, string condition)
        {
            try
            {
                var oracle = new OracleConnectionClass();
                oracle.openConnection();
                var dt = new DataTable();
                var cmd = new OracleCommand(
                    string.Format("select deref(language).name from {0} where {1}"
                    , table, condition), oracle.conn);
                cmd.CommandType = CommandType.Text;
                new OracleDataAdapter(cmd).Fill(dt);
                var lang = new List<ProgLanguage>();
                foreach (DataRow row in dt.Rows)
                {
                    lang.Add(new ProgLanguage(row[0].ToString()));
                }
                return lang;
            }
            catch (Exception)
            {

            }
            return null;
        }

        /// <summary>
        /// zak
        /// radi i za oracle
        /// </summary>
        /// <param name="language"></param>
        public void delete(ProgLanguage language)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    DataConfig.db.Delete(language);
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd =
                            new OracleCommand(
                                string.Format("delete from languages where name = '{0}'",
                                language.Name), oracle.conn) { CommandType = CommandType.Text };
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "delete from competitionslanguages where deref(language).name is null";
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "delete from applicationslanguages where deref(language).name is null";
                        cmd.ExecuteNonQuery();

                        oracle.closeConnection();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
            }
            }

        /// <summary>
        /// zak
        /// radi i za orale
        /// </summary>
        /// <param name="language"></param>
        /// <param name="name"></param>
        public void update(ProgLanguage language, string name)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    ProgLanguage updatedLanguage = (ProgLanguage)DataConfig.db.QueryByExample(language)[0];
                    updatedLanguage.Name = name;
                    DataConfig.db.Store(updatedLanguage);
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand()
                        {
                            CommandType = CommandType.Text,
                            Connection = oracle.conn,
                            CommandText =
                                string.Format("update languages set name='{0}' where name='{1}'", name,
                                              language.Name)
                        };
                        cmd.ExecuteNonQuery();
                        oracle.closeConnection();

                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
            }


        }
    }
}